/*
 *  DatabaseModel.java 
 *    
 *    Copyright 2012 YAWP Project Team
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package com.googlecode.yawp.model;

import java.util.Calendar;
import java.util.List;
import java.util.ArrayList;
import java.sql.*;
import java.io.*;

/**
 * This is a concrete implementation of YawpModel using an SQLite database
 *  for persistent storage.
 * 
 * @author Dan Spuches, Grace Hopkins
 *
 */
public class DatabaseModel extends Model {
	/** Table identifiers for the database. */
	private static final String USER_TABLE = "user";
	private static final String WEIGHT_TABLE = "weight";
	
	/** Database connection initialized once and used as needed */
    private Connection dbConnection; 
    
    /** User information. NOTE: this needs to be kept in sync
     * Use setUserData to set these values, rather than setting individually.
     */
    private String dbUserName = "";
    private int dbUserId = -1;
    private Preferences userPrefs;
    private boolean hasActiveUser = false;
   	   
	/**
	 * Public default constructor for the YawpDatabaseModel class.
	 * <p> Postconditions: database connection is established
	 *                     database is initialized with tables (if needed)
	 * Known Issue: SQLException handling?
	 */
	public DatabaseModel() {
	    DatabaseSingleton singleton = DatabaseSingleton.getDatabaseSingleton();
	    this.dbConnection = singleton.getConnection(false);
	    
	    initDatabase();
	}
	
	/** 
	 * <p> Precondition: User has been selected
	 * @param	userName	The name we checking for existence.
	 * @return  True/False	Name exists or does not.
	 */
	@Override
	public boolean isExistingUser(String userName) {
		int rkey = this.getUserKey(userName);
		
		return (rkey < 0) ? false : true;
	}

	
	/** Indicate if a weight entry for a specific date exists
	 * <p> Precondition: User has been selected
	 * @param date  	The date of interest
	 * @return true	Weight entry exists for the date
	 * @return false	Weight entry does NOT exist.
	 */
	@Override
	public boolean hasExistingWeightEntry(Calendar date) {
		if (!this.hasActiveUser()) {
			return false;
		}
		
		int rowId = this.findWeightEntry(date);
		return (rowId < 0 ? false : true);
	}
	
	/** Create a user
	 * <p> Precondition: name is a valid user name
	 * <p> Precondition: the name does not already exist
	 * <p> Postcondition: user name and preferences stored in database
	 * <p> Postcondition: class variables are updated with user information
	 * Known Issue: setting and storing of preferences not implemented
	 * @param	name	     User name
	 * @param   preferences  User preferences
	 */
	@Override
	public void createUser(String name, Preferences preferences) throws Exception {
		if (this.isExistingUser(name)) {
			throw new Exception("The user name " + name + " already exists.");
		}
		try {
			// Add user name to the database
			Statement stat = dbConnection.createStatement();
	        stat.executeUpdate("insert into " + USER_TABLE + 
			                   " (uname) values ('" + 
			                   name + "');");
	    } catch (SQLException e) {
	    	e.printStackTrace();
	    }

		// Update user's preferences in the database
		this.writeUserPrefs(preferences, name);
			
		this.setCurrentUser(name);
	}

	/** Set the Current User
	 * <p> Precondition: user name exists
	 * <p> Postcondition: class variables updated with user information
	 * @param	userName	The user name to set
	 */
	@Override
	public void setCurrentUser(String userName) throws Exception {
		int foundUserId = this.getUserKey(userName);
	    if (foundUserId < 0) {
	    	throw new Exception("The user name " + userName + " does not exists.");
        }
	    
	    Preferences temp_prefs = this.readUserPrefs(userName);
       
        this.setUserData(userName, temp_prefs, foundUserId);
        
        // notify view that something changed
        setChanged();
        notifyObservers();
	}
  	
	/**
	 * Check if the Database has an active user set
	 * @return true if user is active
	 */
	@Override
	public boolean hasActiveUser() {
		return this.hasActiveUser;
	}

	/**
	 * Set the Current User Flag
	 * @param Current User Flag
	 */
	@Override
	protected void setHasActiveUser(boolean value) {
		this.hasActiveUser = value;
	}

	/**
	 * Save Current User Data
	 * Not currently implemented
	 */
	@Override
	public void saveUserData() {
		// Not used in this implementation; data as saved as it is set.
	}

	/**
	 * Clean Shut down of the database
	 */
	@Override
	public void shutdown() {
		try {
		    dbConnection.close();
		} catch (SQLException e) {
	    	e.printStackTrace();
	    }
	}

	/**
	 * Get Current Users Preferences from the Database
	 * @return Preferences
	 */
	@Override
	public Preferences getPreferences() {
		if (!this.hasActiveUser()) {
			return null;
		}
		
		return this.userPrefs;
	}
	
	/**
	 * Get Current Users Name
	 * @return Name
	 */
	@Override
	public String getUserName() {
		if (!this.hasActiveUser()) {
			return "";
		}
		
		return this.dbUserName;
	}

	/**
	 * Get Current Users Units
	 * @return Units
	 */
	@Override
	public Preferences.Units getUserUnits() {
		return userPrefs.getUnits();
	}

	/**
	 * Get Current Users Target Weight
	 * @return Weight
	 */
	@Override
	public double getUserTargetWeight() {
		if (!this.hasActiveUser()) {
			return 0;
		}
		
		return this.userPrefs.getTargetWeight();
	}

	/**
	 * Get Current Users Height
	 * @return Height
	 */
	@Override
	public double getUserHeight() {
		if (!this.hasActiveUser()) {
			return 0;
		}
		
		return this.userPrefs.getHeight();
	}

	/**
	 * Set Current Users Name
	 * @param userName
	 */
	@Override
	public void setUserName(String userName) {
		if (!this.hasActiveUser()) {
			return;
		}
		
		try {
			Statement stat = dbConnection.createStatement();
			// System.out.println("DEBUG: Changing username from " + this.dbUserName +
			//		             " to " + userName);
			stat.executeUpdate("update " + USER_TABLE +
				   " set uname='" + userName + "' " +
				   " where ROWID='" + this.dbUserId + "';");
		} catch (SQLException e) {
	    	e.printStackTrace();
		}
	    
		this.setUserData(userName, this.userPrefs, this.dbUserId);

		// notify view that something changed
        setChanged();
        notifyObservers();
		
	}

	/**
	 * Set Current Users Units
	 * @param units
	 */
	@Override
	public void setUserUnits(Preferences.Units units) {
		if (!this.hasActiveUser()) {
			return;
		}
		
		userPrefs.setUnits(units);
		
		writeUserPrefs(this.userPrefs, this.dbUserName);
		
		// notify view that something changed
        setChanged();
        notifyObservers();
	}

	/**
	 * Set Current Users Target Weight
	 * @param weight
	 */
	@Override
	public void setUserTargetWeight(double weight) {
		if (!this.hasActiveUser()) {
			return;
		}
		
		this.userPrefs.setTargetWeight(weight);
	
		this.writeUserPrefs(this.userPrefs, this.dbUserName);
		
		// notify view that something changed
        setChanged();
        notifyObservers();
	}

	/**
	 * Set Current Users Height
	 * @param height 
	 */
	@Override
	public void setUserHeight(double height) {
		if (!this.hasActiveUser()) {
			return;
		}
		
		this.userPrefs.setHeight(height);
		
		this.writeUserPrefs(this.userPrefs, this.dbUserName);
		
		// notify view that something changed
        setChanged();
        notifyObservers();
	}
	
	/**
	 * Set Current Users Preferences
	 * @param prefs
	 */
	@Override
	public void setPreferences(Preferences prefs) {
		if (!this.hasActiveUser()) {
			return;
		}
		
		this.userPrefs = prefs;
		
		this.writeUserPrefs(prefs, this.dbUserName);
		
		// notify view that something changed
        setChanged();
        notifyObservers();
	}
	
	/**
	 * Get the specified day's weights
	 * 
	 * @param date - date to get weight for
	 * @return Weight Entry for the specified date
	 */
	public double getDayWeight(Calendar date) {
		double returnWeight = 0;
		
		if (!this.hasActiveUser()) {
			return returnWeight;
		}
		
		long dateMilli = this.getDBTime(date);
		
		try {
			Statement stat = dbConnection.createStatement();
			ResultSet rs = stat.executeQuery("select * from " + WEIGHT_TABLE + 
					" where ukey ='" + dbUserId +
					"' AND date ='" + dateMilli +
					"';");
			while (rs.next()) {
				returnWeight = rs.getDouble("weight");
				// System.out.println("DEBUG: Weight Result: " + returnWeight);
				if (userPrefs.getUnits() == Preferences.Units.US){
					returnWeight = DataEntry.kilogramsToPounds(returnWeight);
				}
				// TODO: Some formatting to cut off the way too many digits after DP
				break;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return returnWeight;
	}
	
	
	/** public getMonthWeight
	 * <p> Invariant: Month is 0 - 11
	 * @return List of weights, ordered by date (first is earliest
	 *             on calendar; last entry in list is latest on calendar
	 */
	@Override
	public List<WeightEntry> getMonthWeights(int month, int year) {
		// Set up first date and last date
		Calendar startDay = Calendar.getInstance();
	    startDay.set(year, (month - 1), 1);

		int lastDay = startDay.getActualMaximum(Calendar.DAY_OF_MONTH);
		Calendar endDay = Calendar.getInstance();
		endDay.set(year, month - 1, lastDay);
		
		return(this.getWeightsBetweenDates(startDay, endDay));
	}

	/** public getYearWeights
	 * @return List of weights, ordered by date (first is earliest
	 *             on calendar; last entry in list is latest on calendar
	 */
	@Override
	public List<WeightEntry> getYearWeights(int year) {
		// Set up first date and last date
		Calendar startDay = Calendar.getInstance();
	    startDay.set(year, Calendar.JANUARY, 1);

		Calendar endDay = Calendar.getInstance();
		endDay.set(year,  Calendar.DECEMBER, 31);
	
		return(this.getWeightsBetweenDates(startDay, endDay));
	}

	/**
	 * Get Current Users Status
	 * @return Status
	 * Not currently implemented
	 */
	@Override
	public String getUserStatus() {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * Add a Weight Entry to the Database
	 * @param date of entry
	 * @param weight of entry
	 */
	@Override
	public void addWeight(Calendar date, double weight) throws Exception {
		if (!this.hasActiveUser()) {
			throw new Exception("There is no currently active user.");
		}
		
		// Weights are stored in the database in kilograms.
		double kgWeight = weight;

		if (this.userPrefs.getUnits() == Preferences.Units.US){
		    kgWeight = DataEntry.poundsToKilograms(weight);
		}
		
		try {
			Statement stat = dbConnection.createStatement();
			int rowId = this.findWeightEntry(date);
			// Replacing existing weight
			if (rowId >= 0) {
				String whereClause = " where ROWID='" + rowId + "'";
				stat.executeUpdate("update " + WEIGHT_TABLE +
					   " set weight='" + kgWeight + "' " +
      		           whereClause + ";");
			}
			// New entry
			else {
		        stat.executeUpdate("insert into " + WEIGHT_TABLE +
	        		           " values ('" + dbUserId + "', '" +
	        		           this.getDBTime(date) + "', '" +
	        		           kgWeight + "');");
			}
			// System.out.println("DEBUG: Weight Entered: " + kgWeight);		
			// notify view that something changed
	        setChanged();
	        notifyObservers();
	        
	    } catch (SQLException e) {
	    	e.printStackTrace();
	    }
	}
	
	/**
	 * Deletes the weight for the specified date
	 * 
	 * @param date Date of the entry
	 * @throws Exception An exception is thrown if there were issues encountered while deleting
	 * the weight entry.
	 */
	@Override
	public void deleteWeight(Calendar date) throws Exception {
		if (!this.hasActiveUser()) {
			throw new Exception("There is no currently active user.");
		}
		
		try {
		    long millidate = this.getDBTime(date);
		    Statement stat = dbConnection.createStatement();
		    stat.executeUpdate("delete from " +
		            WEIGHT_TABLE +
					" where ukey='" + dbUserId +
					"' AND date='" + millidate +
					"';");    
	    } catch (SQLException e) {
    	    e.printStackTrace();
    	}
		
		// notify view that something changed
        setChanged();
        notifyObservers();
	}

	/**
	 * Get Current Users Auto-fill Preference
	 * @return true if set
	 */
	@Override
	public boolean getUserAutoFill() {
		if (!this.hasActiveUser()) {
			return false;
		}
		
		// TODO need to complete this functionality to support the preferences
		// true = set
		return userPrefs.isAutoFill();
	}

	/**
	 * Get Current Users Data View Preference
	 * @return DataView setting
	 */
	@Override
	public Preferences.DataView getUserDataView() {
		if (!this.hasActiveUser()) {
			return null;
		}
		
		// TODO need to complete this functionality to support the preferences
		// 0=day, 1=week, 2=month
		return userPrefs.getDataView();
	}
	
	/** 
	 * Get the earliest date with a weight.
	 * 
	 * @return Calendar
	 */
	@Override
	public Calendar getEarliestDate() {
		if (!this.hasActiveUser()) {
			return null;
		}
		
		// Set up first date and last date
		Calendar startDay = Calendar.getInstance();
	    startDay.set(1970, Calendar.JANUARY, 1);

		Calendar endDay = Calendar.getInstance();
			
		List<WeightEntry> allWeights = this.getWeightsBetweenDates(startDay, endDay);
		
		if (allWeights != null && !allWeights.isEmpty()) {
			WeightEntry firstEntry = allWeights.get(0);
			return (firstEntry.getDate());
		}
		
		return null;
	}

	/** 
	 * clearAllData()
	 * !!! DANGER !!!! This deletes all data in the database and is intended
	 *    to be for testing.
	 */
	@Override
	public void clearAllData() throws Exception {
		Statement stat = dbConnection.createStatement();
		
		try {
			stat.executeUpdate("PRAGMA writable_schema = 1;");
		    stat.executeUpdate("delete from sqlite_master where type = 'table';");
		    stat.executeUpdate("VACUUM;");

		    this.initDatabase();
		
		    // This fails here, but reports ok when run in sqlite3
		    // stat.executeUpdate("PRAGMA INTEGRITY_CHECK;");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 *  Private method to set current user data as a unit
	 *    (Should be a nested class?) 
	 *    @postcondition: Sets hasActiveUser because the data is set to support
	 *                         active user condition
	 */
	private void setUserData(String name, Preferences prefs, int dbRowId) {
		this.dbUserName = name;
		this.dbUserId = dbRowId;
		this.userPrefs = prefs;
		
		this.setHasActiveUser(true);
		
		// notify view that something changed
        setChanged();
        notifyObservers();
	}
		
	/**
	 *  Private method to find a weight entry AND provide the row where
	 * the weight was found. This allows methods in this class to find
	 * out if the weight exists and have the row information to continue
	 * with the processing.
	 * <p> Precondition: User has been selected
	 * @param	date	 The date of interest.
	 * @return  < 0      Does not exist
	 * @return  >= 0     Database row id where the entry was found
	 */
	private int findWeightEntry(Calendar date) {
		int count = 0;
		int rowId = -1;
		
		if (!this.hasActiveUser()) {
			return rowId;
		}

		try {
		    long millidate = this.getDBTime(date);
		    Statement stat = dbConnection.createStatement();
		    ResultSet rs = stat.executeQuery("select ROWID from " +
		            WEIGHT_TABLE +
					" where ukey='" + dbUserId +
					"' AND date='" + millidate +
					"';");
	
	        while (rs.next() && (count < 1)) {
	          count++;
	          rowId = rs.getInt("ROWID");
	        }
	        rs.close();
	    } catch (SQLException e) {
    	    e.printStackTrace();
    	}
		
		return rowId;
	}

	/**
	 *  Private method to save preferences to the database.
	 */
	private void writeUserPrefs(Preferences prefs, String userName) {
		int ukey = this.getUserKey(userName);
		if (ukey < 0) {
			// System.out.println("DEBUG OOPS: could not find " + userName + " in the database");
			return;
		}
		
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		
		try {
		    ObjectOutputStream oout = new ObjectOutputStream(baos);
	        oout.writeObject(prefs);
	        oout.close();
	        byte[] dbSavePrefs = baos.toByteArray();
	    
	        try {
	        	String dbCmd = new String();
	        	dbCmd = "update " + USER_TABLE + " set uprefs = ? " +
	        	        "where ukey = '" + ukey + "';";

	        	PreparedStatement prep = dbConnection.prepareStatement(dbCmd);
	        	prep.setBytes(1, dbSavePrefs);
	            prep.executeUpdate();
	            prep.close();       	
	        } catch (SQLException e) {
	    	    e.printStackTrace();
	        }
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 *  Private method to read preferences to the database.
	 */
	private Preferences readUserPrefs(String userName) {
		Preferences returnPrefs = null;
		
		try {
		    Statement stat = dbConnection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from " + USER_TABLE + 
				" where uname like '" + userName +
				"';");

		    while (rs.next()) {
		    	byte[] dbSavedPrefs = rs.getBytes("uprefs");
		    	if (dbSavedPrefs != null) {
		    		// System.out.println("DEBUG: found saved prefs");
		    		
		    		ByteArrayInputStream bais = new ByteArrayInputStream(dbSavedPrefs);
		    		try {
		    		    ObjectInputStream objectIn = new ObjectInputStream(bais);
		    		    try {
		    	            returnPrefs = (Preferences) objectIn.readObject();
		    	            break;
		    		    } catch (ClassNotFoundException e) {
		    		    	e.printStackTrace();
		    		    }
		    		} catch (IOException e) {
		    			e.printStackTrace();
		    		}
		    	}
		    	rs.close();
		    	}
		    } catch (SQLException e) {
	    	    e.printStackTrace();
	    	}
		    
		return returnPrefs;
	}
  	
	/**
	 *  Private method to get the database key for the specified user
	 * 
	 */
	private int getUserKey(String userName) {
		try {
		    Statement stat = dbConnection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from " + USER_TABLE + 
				" where uname like '" + userName +
				"';");
	
		    while (rs.next()) {
        	    int rkey = rs.getInt("ukey");
 
        	    // System.out.println("DEBUG: key = " + rkey); // TODO: Debug
        	    rs.close();
           	    return rkey;
		    }
		    rs.close();
		} catch (SQLException e) {
    	    e.printStackTrace();
        }
		
		return -1;
 }
	
	/**
	 *  Private method to normalize time by zeroing out minutes, hours and
	 * seconds
	 */
	private long getDBTime(Calendar date) {
		Calendar working = Calendar.getInstance();
		working.setLenient(false);
		working.clear();
		
		int year = date.get(Calendar.YEAR);
		int month = date.get(Calendar.MONTH);
		int day = date.get(Calendar.DAY_OF_MONTH);
		working.set(year, month, day);
		
		return working.getTimeInMillis();
	}
	

	/**
	 * Get Current Users Weights Between Two Dates 
	 * @param startDay
	 * @param endDay
	 * @return List of Weights
	 */
	private List<WeightEntry> getWeightsBetweenDates(Calendar startDay, Calendar endDay) {	
		ArrayList<WeightEntry> toReturn = new ArrayList<WeightEntry>();

		long startMilli = this.getDBTime(startDay);
		long endMilli = this.getDBTime(endDay);

		// Use SQL between
		// System.out.println("DEBUG: Looking for between " + startMilli + " and " + endMilli);
		try {
			Statement stat = dbConnection.createStatement();
			ResultSet rs = stat.executeQuery("select * from " + WEIGHT_TABLE + 
					" where ukey='" + dbUserId +
					"' AND date between '" + startMilli +
					"' AND '" + endMilli + 
					"' order by date;");
			Calendar calendar = Calendar.getInstance();
			while (rs.next()) {
				double theWeight = rs.getDouble("weight");
				// System.out.println("DEBUG: Weight Result: " + theWeight);
				if (userPrefs.getUnits() == Preferences.Units.US){
					theWeight = DataEntry.kilogramsToPounds(theWeight);
				}
				// TODO: Some formatting to cut off the way too many digits after DP
				long milliDate = rs.getLong("date");
				calendar.clear();
				calendar.setTimeInMillis(milliDate);

				// Add a weight entry with the date and weight just found
				Calendar nextDate = (Calendar) calendar.clone();
				WeightEntry nextWeight = new WeightEntry(nextDate, theWeight, false);
				toReturn.add(nextWeight);      
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return toReturn;
	}
	
	/** 
	 * Create the necessary tables in the database
	 * 
	 */
	private void initDatabase() {
	  try {
	        Statement stat = this.dbConnection.createStatement();
	        stat.executeUpdate("create table if not exists " +
	                  USER_TABLE + 
	                  " (ukey INTEGER PRIMARY KEY, " +
	        		  "uname TEXT COLLLATE NOCASE, " +
	                  "uprefs BLOB);");
	        stat.executeUpdate("create table if not exists " +
	                  WEIGHT_TABLE + 
	                  " (ukey INTEGER, date INTEGER, " +
	        		  "weight REAL);");
		    } catch (SQLException e) {
		    	e.printStackTrace();
	      }
	}

	
}
